package com.salton123.soulove.sailfish.db

import androidx.lifecycle.LiveData
import androidx.room.Dao
import androidx.room.Insert
import androidx.room.OnConflictStrategy
import androidx.room.Query

/**
 * Time:8/26/21 4:39 PM
 * Author:
 * Description:
 */
@Dao
interface VideoDao {

    @Insert(onConflict = OnConflictStrategy.IGNORE)
    suspend fun add(item: VideoItem): Long

    @Query(
        "SELECT * FROM video_item" +
            " ORDER BY " +
            "CASE WHEN :isAsc = 1 AND :sortType = 'createdAt' THEN createdAt END ASC, " +
            "CASE WHEN :isAsc = 0 AND :sortType = 'createdAt' THEN createdAt END DESC, " +
            "CASE WHEN :isAsc = 1 AND :sortType = 'size' THEN size END ASC, " +
            "CASE WHEN :isAsc = 0 AND :sortType = 'size' THEN size END DESC"
    )
    fun loadAll(isAsc: Boolean, sortType: String): LiveData<MutableList<VideoItem>>

//    @Query("select * from video_item ORDER by createdAt desc limit 1 ")
//    fun loadLatest(): LiveData<VideoItem?>
//
//    @Query("select * from video_item where filePath =:filePath ")
//    suspend fun load(filePath: String): VideoItem?

    @Query("delete from video_item where filePath =:filePath ")
    suspend fun delete(filePath: String): Int

//    @Update(onConflict = OnConflictStrategy.REPLACE)
//    suspend fun update(item: VideoItem)

//    @Query("select * from video_item ORDER by updatedAt desc limit 3 ")
//    fun loadBanner(): LiveData<MutableList<VideoItem>?>

//    @Query("select * from video_item ORDER by createdAt desc limit 10 ")
//    fun loadRecent(): LiveData<MutableList<VideoItem>?>

    @Query("select dirName from video_item group by dirName ORDER by MAX(createdAt) desc")
    fun loadDirs(): LiveData<MutableList<String>>

    @Query("select * from video_item where dirName =:dirName ORDER by createdAt desc")
    suspend fun loadbyDir(dirName: String): MutableList<VideoItem>

//    @Query("select * from video_item where dirName =:dirName ORDER by size desc")
//    fun loadDir(dirName: String): LiveData<MutableList<VideoItem>>

    @Query(
        "SELECT * FROM video_item where dirName =:dirName" +
            " ORDER BY " +
            "CASE WHEN :isAsc = 1 AND :sortType = 'createdAt' THEN createdAt END ASC, " +
            "CASE WHEN :isAsc = 0 AND :sortType = 'createdAt' THEN createdAt END DESC, " +
            "CASE WHEN :isAsc = 1 AND :sortType = 'size' THEN size END ASC, " +
            "CASE WHEN :isAsc = 0 AND :sortType = 'size' THEN size END DESC"
    )
    fun loadDataByDir(dirName: String, isAsc: Boolean, sortType: String): LiveData<MutableList<VideoItem>>

    @Query(
        "select * from video_item where dirName like '%'||:dirName ||'%' and name like '%'||:word ||'%' ORDER by createdAt desc")
    suspend fun fluzzySearch(dirName: String, word: String): MutableList<VideoItem>

    @Insert(onConflict = OnConflictStrategy.IGNORE)
    suspend fun addDir(dir: DirItem): Long

//    @Query("select * from dir_item where dirName =:dirName ORDER by createdAt desc limit 1")
//    suspend fun loadDir(dirName: String): DirItem?
//
//    @Query("select dirName from dir_item ORDER by createdAt desc")
//    fun loadDirInfos(): LiveData<MutableList<String>>

    @Query("select * from video_item where filePath =:filePath limit 1")
    suspend fun loadItem(filePath: String): VideoItem?

    @Query("select sum(size) from video_item")
    fun totalSize(): LiveData<Long?>?
}